/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.sql.*;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import POJO.HocSinh;
import java.awt.Image;
import java.util.ArrayList;
/**
 *
 * @author CUCAINHO
 */
public class HocSinhDAO {

   
    public static void main(String[] args) {
        // TODO code application logic here
       
        HocSinhDAO hs = new HocSinhDAO();
        hs.SearchHocSinh(1, " where MaHS = 1");
        /*hs.GetListHocSinh(6);
        Date d = new Date(1990,10,10);
        Image img = null ;
        HocSinh hss = new HocSinh(6,"Tran thi anna thi ho",d,img,6,"Lop 11HCA2");
        
      */
    }
    
    public static Connection ConnectData(){ 
        Connection con =null;  
       
           try
           {
                System.out.println("111");
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");               
            	con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=HocSinh;user=sa;password=123456");             
                if(con!=null) 
                {
                    
                    System.out.println("Connection Successful!");
                    return con;
                }
           }
           catch(Exception e)
           {
               System.out.println("Loi connect");
               JOptionPane.showMessageDialog(new JFrame(), e.getMessage(), "Error Message", JOptionPane.ERROR_MESSAGE);
               e.printStackTrace();
               System.out.println("Error Trace in getConnection() : " + e.getMessage());
           }
           return null;
            
              
                      /*  Statement stmt = con.createStatement();
                        ResultSet rs = stmt.executeQuery("select * FROM student");
                        //int rowcount = stmt.executeUpdate("insert into student(studentid, studentname) values (1002,'New Student')");
                        //int rowcount = stmt.executeUpdate("delete student where studentid>500");
                        //System.out.println("Record Affected:" + rowcount);

                        while (rs.next())
                        {
                                System.out.println("StudentID: " + rs.getInt(1));
                                System.out.println("StudentName: " + rs.getString(2));
                        }

                        System.out.println("Connected To SQL Server 2005");
                        stmt.close();
                        con.close();*/

      }

    
    
    
    public static ArrayList<HocSinh> GetListHocSinh(int option){ 
         ArrayList<HocSinh> lstHocSinh = new ArrayList<HocSinh>();
        String sSQL = "select * from HocSinh order by ";
        switch(option){
            case 1: // MaHS Tang
                sSQL += "MaHS asc";
                break;
            case 2: // MaHS Giam
                sSQL += "MaHS desc";
            case 3: // TenHS Tang
                sSQL += "TenHS asc";
                break;
            case 4: // TenHS Giam
                sSQL += "TenHS desc";
                break;
            case 5: // Diem TB Tang
                sSQL += "DiemTB asc";
                break;
            case 6: // DiemTB Giam
                sSQL += "DiemTB desc";
                break;
        }
        try
        {
            Connection con = ConnectData();           
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sSQL);
            //int rowcount = stmt.executeUpdate("insert into student(studentid, studentname) values (1002,'New Student')");
            //int rowcount = stmt.executeUpdate("delete student where studentid>500");
            //System.out.println("Record Affected:" + rowcount);
            while (rs.next())
            {
                    int MaHS = rs.getInt("MaHS");
                    String TenHS = rs.getString("TenHS");
                    Date NgaySinh = rs.getDate("NgaySinh");
                    Image HinhAnh = null;
                    float DiemTB = rs.getFloat("DiemTB");
                    String GhiChu = rs.getString("GhiChu");
                    //Image img = Image(rs.getObject("HinhAnh"));
                    
                    HocSinh hs = new HocSinh(MaHS,TenHS,NgaySinh,null,DiemTB,GhiChu);
                    lstHocSinh.add(hs);      
                    System.out.println("StudentID: " + rs.getInt(1));
                    System.out.println("StudentName: " + rs.getString(2));
                    System.out.println("Ngay Sinh: " + rs.getDate(3).toString());
                    System.out.println("diem tb: " + rs.getFloat(5));
                    System.out.println("ghi chu: " + rs.getString(6));
            }

            System.out.println("Connected To SQL Server 2005");
            stmt.close();
            con.close();
            return lstHocSinh;
        }catch(Exception ex)
        {
            System.out.println("Loi doc data!" + ex.toString());
            return null;
        }
        
        
        
        
    }
    
    public static ArrayList<HocSinh> SearchHocSinh(int option , String where){ 
         ArrayList<HocSinh> lstHocSinh = new ArrayList<HocSinh>();
        String sSQL = "select * from HocSinh " +where+" order by ";
        switch(option){
            case 1: // MaHS Tang
                sSQL += "MaHS asc";
                break;
            case 2: // MaHS Giam
                sSQL += "MaHS desc";
            case 3: // TenHS Tang
                sSQL += "TenHS asc";
                break;
            case 4: // TenHS Giam
                sSQL += "TenHS desc";
                break;
            case 5: // Diem TB Tang
                sSQL += "DiemTB asc";
                break;
            case 6: // DiemTB Giam
                sSQL += "DiemTB desc";
                break;
        }
        try
        {
            Connection con = ConnectData();           
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sSQL);
            //int rowcount = stmt.executeUpdate("insert into student(studentid, studentname) values (1002,'New Student')");
            //int rowcount = stmt.executeUpdate("delete student where studentid>500");
            //System.out.println("Record Affected:" + rowcount);
            while (rs.next())
            {
                    int MaHS = rs.getInt("MaHS");
                    String TenHS = rs.getString("TenHS");
                    Date NgaySinh = rs.getDate("NgaySinh");
                    Image HinhAnh = null;
                    float DiemTB = rs.getFloat("DiemTB");
                    String GhiChu = rs.getString("GhiChu");
                    //Image img = Image(rs.getObject("HinhAnh"));
                    
                    HocSinh hs = new HocSinh(MaHS,TenHS,NgaySinh,null,DiemTB,GhiChu);
                    lstHocSinh.add(hs);      
                    System.out.println("StudentID: " + rs.getInt(1));
                    System.out.println("StudentName: " + rs.getString(2));
                    System.out.println("Ngay Sinh: " + rs.getDate(3).toString());
                    System.out.println("diem tb: " + rs.getFloat(5));
                    System.out.println("ghi chu: " + rs.getString(6));
            }

            System.out.println("Connected To SQL Server 2005");
            stmt.close();
            con.close();
            return lstHocSinh;
        }catch(Exception ex)
        {
            System.out.println("Loi doc data!" + ex.toString());
            return null;
        }
        
        
        
        
    }
    
    
    public static int AddHocSinh(HocSinh hs){
        int kq = -1;
        String sSQL = "INSERT INTO HocSinh (MaHS,TenHS,NgaySinh,HinhAnh,DiemTB,GhiChu) VALUES(?,?,?,?,?,?)";
	
        try
        {
            Connection con = ConnectData();
            PreparedStatement pst = con.prepareStatement(sSQL);
           
            pst.setInt(1, hs.GetMaHS());
            pst.setString(2,hs.GetHoTen());
            pst.setObject(3,hs.GetNgaySinh());
            pst.setObject(4, null);
            pst.setFloat(5, hs.GetDiem());
            pst.setString(6, hs.GetghiChu());
            
    
          kq =   pst.executeUpdate();
                
          //  int rowcount = stmt.executeUpdate("insert into student( ) values (1002,'New Student')");
           // int rowcount = stmt.executeUpdate("delete student where studentid>500");
            //stmt.close();
            con.close();
            return kq;
        }catch(Exception ex)
        {
            System.out.println("Eror in Add HS!" + ex.toString());
            
        }
        
        return kq;
    }
    
    
    public static int UpdateHocSinh(HocSinh hs){
        int kq = -1;
        String sSQL = "Update HocSinh set TenHS = ? , NgaySinh = ? , HinhAnh = ? , DiemTB = ? , GhiChu = ? where MaHS = ?";
	
        
        try
        {
            Connection con = ConnectData();
            PreparedStatement pst = con.prepareStatement(sSQL);
           
           
            pst.setString(1,hs.GetHoTen());
            pst.setObject(2,hs.GetNgaySinh());
            pst.setObject(3, null);
            pst.setFloat(4, hs.GetDiem());
            pst.setString(5, hs.GetghiChu()); 
            pst.setInt(6, hs.GetMaHS());
            
            
    
           kq =  pst.executeUpdate();
                
          //  int rowcount = stmt.executeUpdate("insert into student( ) values (1002,'New Student')");
           // int rowcount = stmt.executeUpdate("delete student where studentid>500");
            //stmt.close();
            con.close();
            return kq;
            
        }catch(Exception ex)
        {
            System.out.println("Error in update HS.!" + ex.toString());
            
        }
        
        return kq;
    }
    
     public static int DeleteHocSinh(int MaHS){
          int kq = -1;
        String sSQL = "Delete HocSinh where MaHS = ?";
	
        try
        {
            Connection con = ConnectData();
            PreparedStatement pst = con.prepareStatement(sSQL);
           
            pst.setInt(1, MaHS);
           kq =  pst.executeUpdate();
                
          //  int rowcount = stmt.executeUpdate("insert into student( ) values (1002,'New Student')");
           // int rowcount = stmt.executeUpdate("delete student where studentid>500");
            //stmt.close();
            con.close();
            return kq;
            
        }catch(Exception ex)
        {
            System.out.println("Loi doc data!" + ex.toString());
            
        }
        
        return kq;
     }
    
     
}


   


